import java.io.File;
import java.io.IOException;
import java.sql.*;
import java.util.Date;
import java.util.*;

import com.narphorium.*;
import com.narphorium.freebase.query.*;
import com.narphorium.freebase.query.io.*;
import com.narphorium.freebase.results.Result;
import com.narphorium.freebase.services.*;
import com.narphorium.freebase.services.exceptions.FreebaseServiceException;

public class fictional_loader {
	private static Connection connect = null;
	private static Statement statement = null;
	private static PreparedStatement preparedStatement = null;
	private static ResultSet resultSet = null;
	
	static void propertyListUpdate(String mql_path,String db_table,String property_col) {
		QueryParser queryParser = new QueryParser();
		ReadService readService = new ReadService();
		Query query = queryParser.parse(new File(mql_path));				
				try {					
						com.narphorium.freebase.results.ResultSet results = readService.read(query,"true");												
						while (results.hasNext()) {
								String insrt = "INSERT INTO "+db_table+" ("+property_col+",mid) VALUES ";
								for (int i=0;i<200;i++) {
									try {
										if (results.hasNext()) {
											Result result = results.next();
											String mid = result.getString("m");
											String name = result.getString("n");
											insrt += "('"+name.replace("'","''")+"'"+",'"+mid+"'),";
										}
									}
									catch( Exception ex ) { ex.printStackTrace();}	
								}
								try {
									String stam = insrt.substring(0,insrt.length()-1);
									connect.createStatement().execute(stam);
								}	
								catch( Exception ex ) { ex.printStackTrace();}
							}
						System.out.println("Done uploading to "+db_table);
				}
				catch (IOException e) {
					e.printStackTrace();
				} catch (FreebaseServiceException e) {
					e.printStackTrace();
				}
	}
				
	
	static void coupleUpdate(String mql_path,String db_table,String column1,String column2) {
		QueryParser queryParser = new QueryParser();
		ReadService readService = new ReadService();
		Query query = queryParser.parse(new File(mql_path));				
				try {
					com.narphorium.freebase.results.ResultSet results = readService.read(query,"true");	
											
						while (results.hasNext()) {											
								String insrt = "INSERT IGNORE INTO "+db_table+" ("+column1+","+column2+") VALUES ";
								boolean flag = true;
								for (int i=0;i<200;i++) {										
									try {
										String char_id1,char_id2;
										Result result = results.next();
										List<Object> coll = result.getCollection("partner");
										Iterator iter = coll.iterator();								
										String mid1	= ((Map<String,String>)iter.next()).get("mid");
										String mid2	= ((Map<String,String>)iter.next()).get("mid");
										ResultSet rs1 = connect.createStatement().executeQuery("SELECT character_id FROM charf WHERE mid='"+mid1+"'");
										rs1.next(); 
										char_id1 = rs1.getString("character_id");
										ResultSet rs2 = connect.createStatement().executeQuery("SELECT character_id FROM charf WHERE mid='"+mid2+"'");
										rs2.next();
										char_id2 = rs2.getString("character_id");
										String q = "('"+char_id1.replace("'", "''")+"','"+char_id2.replace("'", "''")+"'),";
										insrt += q;
											
									}
									catch( Exception ex ) { ex.printStackTrace(); flag = false; }	
								}								
								String stam = insrt.substring(0,insrt.length()-1);
								if (flag)
								{
									try {
										connect.createStatement().execute(stam);
									}
									catch (SQLException ex) { ex.printStackTrace(); } 									
								}									
							}																								
				}
				catch (IOException e) {
					e.printStackTrace();
				} catch (FreebaseServiceException e) {
					e.printStackTrace();
				}
	}
	
	static void executeInsertBatch(String[] insrt) throws Exception{
		Statement stmt = connect.createStatement();
		for (int i=0;i<insrt.length;i++) {
			if (insrt[i].endsWith(","))
				stmt.addBatch(insrt[i].substring(0,insrt[i].length()-1));
		}
		stmt.executeBatch();
	}
	
	static void charUpdate() throws Exception {
		QueryParser queryParser = new QueryParser();
		ReadService readService = new ReadService();
		Query query = queryParser.parse(new File("src\\character.mql"));				
				try {					
					com.narphorium.freebase.results.ResultSet results = readService.read(query,"true");
					Statement stmt = connect.createStatement();
					
					String create_char = "CREATE TABLE IF NOT EXISTS t_charf (mid varchar(45),name varchar(250),height int,weight int, place_of_birth varchar(45),KEY mid (mid)); ";
					String create_img = "CREATE TABLE IF NOT EXISTS t_img (char_mid varchar(45),prop_mid varchar(250),KEY mid (char_mid)); ";
					String create_art = "CREATE TABLE IF NOT EXISTS t_art (char_mid varchar(45),prop_mid varchar(250),KEY mid (char_mid));";
					String create_occ = "CREATE TABLE IF NOT EXISTS t_occ (char_mid varchar(45),prop_mid varchar(250),KEY mid (char_mid));";
					String create_pow = "CREATE TABLE IF NOT EXISTS t_pow (char_mid varchar(45),prop_mid varchar(250),KEY mid (char_mid));";
					String create_org = "CREATE TABLE IF NOT EXISTS t_org (char_mid varchar(45),prop_mid varchar(250),KEY mid (char_mid));";
					String create_spe = "CREATE TABLE IF NOT EXISTS t_spe (char_mid varchar(45),prop_mid varchar(250),KEY mid (char_mid));";
					String create_gen = "CREATE TABLE IF NOT EXISTS t_gen (char_mid varchar(45),prop_mid varchar(250),KEY mid (char_mid));";
					String create_chi = "CREATE TABLE IF NOT EXISTS t_chi (char1_mid varchar(45),char2_mid varchar(45),KEY mid1 (char1_mid),KEY mid2 (char2_mid));";					
					String create_ran = "CREATE TABLE IF NOT EXISTS t_ran (char_mid varchar(45),prop_mid varchar(250),KEY mid (char_mid));";					
					
					stmt.addBatch(create_char);						
					stmt.addBatch(create_img);
					stmt.addBatch(create_art);
					stmt.addBatch(create_occ);
					stmt.addBatch(create_pow);
					stmt.addBatch(create_org);
					stmt.addBatch(create_spe);
					stmt.addBatch(create_gen);
					stmt.addBatch(create_chi);
					stmt.addBatch(create_ran);
					stmt.executeBatch();
					
					int count = 0;
					while (results.hasNext()) {												
						String char_insrt = "INSERT IGNORE INTO t_charf (mid,name,height,weight) VALUES ";
						String img_insrt = "INSERT IGNORE INTO t_img (char_mid,prop_mid) VALUES ";
						String art_insrt = "INSERT IGNORE INTO t_art (char_mid,prop_mid) VALUES ";
						String occ_insrt = "INSERT IGNORE INTO t_occ (char_mid,prop_mid) VALUES ";
						String pow_insrt = "INSERT IGNORE INTO t_pow (char_mid,prop_mid) VALUES ";
						String org_insrt = "INSERT IGNORE INTO t_org (char_mid,prop_mid) VALUES ";
						String spe_insrt = "INSERT IGNORE INTO t_spe (char_mid,prop_mid) VALUES ";
						String gen_insrt = "INSERT IGNORE INTO t_gen (char_mid,prop_mid) VALUES ";
						String chi_insrt = "INSERT IGNORE INTO t_chi (char1_mid,char2_mid) VALUES ";
						String ran_insrt = "INSERT IGNORE INTO t_ran (char_mid,prop_mid) VALUES ";						
						
						for (int i=0;i<200;i++) {
							try {
								if (results.hasNext()) {									
									Result result = results.next();
									count++;									
									String char_height = "0";
									String char_weight = "0";
									String char_mid = result.getString("mid");
									String char_name = result.getString("nam");											
									if (result.getString("weight") != null)
										char_weight = result.getString("weight");
									if (result.getString("weight") != null)
										char_weight = result.getString("height");
									List<Object> imgs = result.getCollection("imgs");
									List<Object> articles = result.getCollection("articles");
									List<Object> children = result.getCollection("children");
									List<Object> powers = result.getCollection("powers");
									List<Object> occupation = result.getCollection("occupation");
									List<Object> orgs = result.getCollection("orgs");
									List<Object> species = result.getCollection("species");
									List<Object> gender = result.getCollection("gender");
									List<Object> rank = result.getCollection("rank");																																			
									
									Iterator iter = imgs.iterator();
									while (iter.hasNext()) {
										try {
											Map<String,String> map = (Map<String,String>)iter.next();
											String img_mid = map.get("mid");
											img_insrt += "('"+char_mid+"','"+img_mid+"'),";
										}
										catch (Exception ex) {ex.printStackTrace();}
									}
									iter = articles.iterator();
									while (iter.hasNext()) {
										try {
											Map<String,String> map = (Map<String,String>)iter.next();
											String art_mid = map.get("mid");
											art_insrt += "('"+char_mid+"','"+art_mid+"'),";
										}
										catch (Exception ex) { ex.printStackTrace(); }
									}
									iter = children.iterator();
									while (iter.hasNext()) {
										try {
											Map<String,String> map = (Map<String,String>)iter.next();
											String child_mid = map.get("mid");								
											chi_insrt += "('"+char_mid+"','"+child_mid+"'),";
										}
										catch (Exception ex) {ex.printStackTrace();}
									}
									iter = powers.iterator();
									while (iter.hasNext()) {
										try {
											Map<String,String> map = (Map<String,String>)iter.next();
											String pow_mid = map.get("mid");								
											pow_insrt += "('"+char_mid+"','"+pow_mid+"'),";
										}
										catch (Exception ex) {ex.printStackTrace();}
									}
									iter = occupation.iterator();
									while (iter.hasNext()) {
										try {
											Map<String,String> map = (Map<String,String>)iter.next();
											String occ_mid = map.get("mid");								
											occ_insrt += "('"+char_mid+"','"+occ_mid+"'),";
										}
										catch (Exception ex) {ex.printStackTrace();}
									}
									iter = orgs.iterator();
									while (iter.hasNext()) {
										try {
											Map<String,String> map = (Map<String,String>)iter.next();
											String org_mid = map.get("mid");								
											org_insrt += "('"+char_mid+"','"+org_mid+"'),";
										}
										catch (Exception ex) {ex.printStackTrace();}
									}
									iter = species.iterator();
									while (iter.hasNext()) {
										try {
											Map<String,String> map = (Map<String,String>)iter.next();
											String spe_mid = map.get("mid");								
											spe_insrt += "('"+char_mid+"','"+spe_mid+"'),";
										}
										catch (Exception ex) {ex.printStackTrace();}
									}
									iter = gender.iterator();
									while (iter.hasNext()) {
										try {
											Map<String,String> map = (Map<String,String>)iter.next();
											String gen_mid = map.get("mid");								
											gen_insrt += "('"+char_mid+"','"+gen_mid+"'),";
										}
										catch (Exception ex) {ex.printStackTrace();}
									}
									iter = occupation.iterator();
									while (iter.hasNext()) {
										try {
											Map<String,String> map = (Map<String,String>)iter.next();
											String occ_mid = map.get("mid");								
											occ_insrt += "('"+char_mid+"','"+occ_mid+"'),";
										}
										catch (Exception ex) {ex.printStackTrace();}
									}
									iter = rank.iterator();
									while (iter.hasNext()) {
										try {
											Map<String,String> map = (Map<String,String>)iter.next();
											String ran_mid = map.get("mid");								
											ran_insrt += "('"+char_mid+"','"+ran_mid+"'),";
										}
										catch (Exception ex) {ex.printStackTrace();}
									}
									
									char_insrt += "('"+char_mid+"','"+char_name.replace("'", "''")+"','"+char_height+"','"+char_weight+"'),";
								}
							}
							catch( Exception ex ) { ex.printStackTrace();}
						}					
						String[] t = {char_insrt,art_insrt,img_insrt,chi_insrt,pow_insrt,
												 occ_insrt,org_insrt,spe_insrt,gen_insrt,occ_insrt,
												ran_insrt};
						executeInsertBatch(t);
					} //end while
					CallableStatement st = connect.prepareCall("{ call LoaderCopy() }");
					st.execute();
				}
				catch (IOException e) {
					e.printStackTrace();
				} catch (FreebaseServiceException e) {
					e.printStackTrace();
				}
			System.out.println("finito");	
	}
	
	public static void main(String[] args) throws Exception {
		if (args.length != 3) {
			System.out.println("Parameters error; expected: connection string, username, password");
			return;
		}
		System.out.println("IMPORTANT: a full import takes at least 60 minutes and must not be interrupted until completion.\nAll of the DB's current data will be overwritten except for explicitly checked entries.\nPress any key to start importing...");		
		System.in.read();
		Class.forName("com.mysql.jdbc.Driver").newInstance ();
		//connect = DriverManager.getConnection("jdbc:mysql://localhost:3306/DbMysql13","DbMysql13","DbMysql13");
		//connect = DriverManager.getConnection("jdbc:mysql://localhost:3306/dbmysql13","root","123456");
		connect = DriverManager.getConnection(args[0],args[1],args[2]);
		
		Statement stmt = connect.createStatement();
		
		try {
			stmt.addBatch("DELETE FROM t_charf");
			stmt.addBatch("DELETE FROM t_art");
			stmt.addBatch("DELETE FROM t_img");
			stmt.addBatch("DELETE FROM t_chi");
			stmt.addBatch("DELETE FROM t_gen");
			stmt.addBatch("DELETE FROM t_spe");
			stmt.addBatch("DELETE FROM t_occ");
			stmt.addBatch("DELETE FROM t_org");
			stmt.addBatch("DELETE FROM t_ran");
			stmt.executeBatch();			
		}
		catch(Exception ex) {  }
		
		stmt.clearBatch();
		
		charUpdate();
		
		try {
			stmt.addBatch("DELETE FROM sibling");
			stmt.addBatch("DELETE FROM married");
			stmt.addBatch("DELETE FROM romantic");		
			stmt.executeBatch();
		}
		catch (Exception ex) {			
			
		}
		
		stmt.clearBatch();
		
		coupleUpdate("src\\romantic.mql","romantic","character1_id","character2_id");
		coupleUpdate("src\\sibling.mql","sibling","character1_id","character2_id");
		coupleUpdate("src\\marriage.mql","married","character1_id","character2_id");
		
		try {
			stmt.addBatch("DELETE FROM place");
			stmt.addBatch("DELETE FROM gender");
			stmt.addBatch("DELETE FROM species");
			stmt.addBatch("DELETE FROM organization");
			stmt.addBatch("DELETE FROM occupation");
			stmt.addBatch("DELETE FROM rank");
			stmt.addBatch("DELETE FROM power");
			stmt.executeBatch();
		}
		catch (Exception ex) {

		}
		
		stmt.clearBatch();
		
		try {
			propertyListUpdate("src\\power.mql","power", "power");
			propertyListUpdate("src\\place.mql","place", "place");
			propertyListUpdate("src\\gender.mql","gender", "gender");
			propertyListUpdate("src\\species.mql","species", "species");
			propertyListUpdate("src\\organization.mql","organization", "organization.name");
			propertyListUpdate("src\\occupation.mql","occupation", "occupation");
			propertyListUpdate("src\\rank.mql","rank", "rank");
		}		
		catch (Exception ex ) {}
		
		CallableStatement st = connect.prepareCall("{ call LoaderCopy() }");
		st.execute();
		System.out.println("Done uploading");
	}
}
